from PyQt5.QtCore import Qt
from 数据库 import de_g_query, alter
from PyQt5.QtWidgets import QApplication, QDialog, QMessageBox,  QTableWidgetItem
import xlwt
import sys
import pymysql


class MainDialog(QDialog):
    def __init__(self, parent=None):
        super(QDialog, self).__init__(parent)
        self.ui = de_g_query.Ui_Dialog()
        self.ui.setupUi(self)
        self.head = ''
        self.chile_Win1 = alter.MainDialog()
        self.agree = 0
        self.wb = None

    def output(self):
        if self.wb:
            if self.x == self.ui.comboBox.currentText():
                self.wb.save(self.x + '.xls')
            else:
                QMessageBox.about(self, ' ', '请先查询相应信息')
        else:
            QMessageBox.about(self, ' ', '请先查询')

    def query(self):
        conn = pymysql.connect(host='127.0.0.1', user='root', passwd='dqzx2020', port=3306, db='design', charset='utf8')
        zonta = []
        name = self.ui.comboBox.currentText()
        self.x = name
        self.wb = xlwt.Workbook()
        ws = self.wb.add_sheet(name + '报表')
        cur = conn.cursor()
        if name == '采购明细':
            sql = "select * from {}".format(name)
        else:
            sql = "select * from {}".format(name[0:-2])
        cur.execute(sql)
        self.head = cur.description
        data = cur.fetchall()
        self.ui.tabWidget.setColumnCount(len(self.head))
        self.ui.tabWidget.setRowCount(len(data))
        x = 0
        for i in self.head:
            zonta.append(str(i[0]))
            ws.write(0, x, str(i[0]))
            x += 1
        self.ui.tabWidget.setHorizontalHeaderLabels(zonta)
        self.zonta = zonta
        for i in range(0, len(data)):
            for j in range(0, len(data[i])):
                item = QTableWidgetItem(str(data[i][j]))
                item.setTextAlignment(Qt.AlignCenter | Qt.AlignBottom)
                item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled)
                self.ui.tabWidget.setItem(i, j, item)

                ws.write(i+1, j, str(data[i][j]))
        if name in ['采购明细', '采购清单']:
            self.ui.label2.setText('目前共有' + str(len(data)) + '份采购明细单')
        elif name in ['供应商信息', '员工信息']:
            self.ui.label2.setText('目前共有' + str(len(data)) + '名' + name[0:-2])
        else:
            self.ui.label2.setText('目前共有' + str(len(data)) + '份' + name[0:-2])
        conn.close()

    def append(self, name):
        self.name = name
        self.ui.label.setText("<html><head/><body><p align=\"center\">欢迎您,{}管理员</p></body></html>".format(self.name))
        self.show()

    def alter(self):
        column = self.ui.tabWidget.currentColumn()
        result = []
        if self.head:
            if column > 0:
                row = self.ui.tabWidget.currentRow()
                for i in range(len(self.zonta)):
                    result.append(str(self.ui.tabWidget.item(row, i).text()))
                self.chile_Win1.append(self.zonta, result, column, self.ui.comboBox.currentText())
                self.chile_Win1.exec_()
            else:
                QMessageBox.about(self, ' ', '请先选择要修改的单元格(编号不能修改)')
        else:
            QMessageBox.about(self, ' ', '请先查询相应信息')

    def delete(self):
        conn = pymysql.connect(host='127.0.0.1', user='root', passwd='dqzx2020', port=3306, db='design', charset='utf8')
        if self.head:
            if self.ui.tabWidget.currentColumn() == 0:
                if self.agree == 1:
                    num_k = self.zonta[0]
                    num_v = self.ui.tabWidget.item(self.ui.tabWidget.currentRow(), 0).text()
                    if self.ui.comboBox.currentText() == '采购明细':
                        sql = "DELETE FROM {} WHERE {} = {}".format(self.ui.comboBox.currentText(), num_k, num_v)
                    else:
                        sql = "DELETE FROM {} WHERE {} = {}".format(self.ui.comboBox.currentText()[0:-2], num_k, num_v)
                    cur = conn.cursor()
                    cur.execute(sql)
                    QMessageBox.about(self, ' ', '删除成功')
                    self.agree -= 1
                    conn.commit()
                    conn.close()
                else:
                    QMessageBox.about(self, ' ', '再按一次确定删除')
                    self.agree += 1
            else:
                QMessageBox.about(self, ' ', '删除时只能选择相应的编号')
                self.agree -= 1
        else:
            QMessageBox.about(self, ' ', '请先查询相应信息')
            self.agree -= 1
if __name__ == '__main__':
    myapp = QApplication(sys.argv)
    myDlg = MainDialog()
    myDlg.show()
    sys.exit(myapp.exec_())